From 42e93dc51582bf3f7182f955a39b1034ed483a38 Mon Sep 17 00:00:00 2001
From: "Karl O. Pinc kop@karlpinc.com" <kop@karlpinc.com>
Date: Thu, 13 Aug 2026 16:30:33 +0000
Subject: [PATCH] Cannot be AnimID in WATCHES before being born

---
 .../lib/triggers/create/biography_data.m4     | 30 +++++++++++++++++++
 db/schemas/lib/triggers/create/watches.m4     | 29 ++++++++++++++++++
 2 files changed, 59 insertions(+)

diff --git a/db/schemas/lib/triggers/create/biography_data.m4 b/db/schemas/lib/triggers/create/biography_data.m4
index b99c0f3..5817429 100644
--- a/db/schemas/lib/triggers/create/biography_data.m4
+++ b/db/schemas/lib/triggers/create/biography_data.m4
@@ -474,6 +474,36 @@ CREATE OR REPLACE FUNCTION biography_data_func ()
         END IF;
       END IF;
 
+      -- Cannot have a watch period before the individual was born.
+      IF NEW.birthdate > OLD.birthdate THEN
+        SELECT watches.wid, watches.commid, watches.date, watches.type
+          INTO a_wid      , a_commid      , a_date      , a_type
+          FROM watches
+          WHERE watches.animid = NEW.animid
+                AND watches.date < NEW.birthdate
+          ORDER BY watches.date;  -- consistency
+        IF FOUND THEN
+          RAISE EXCEPTION integrity_constraint_violation USING
+                MESSAGE = 'Error on UPDATE of BIOGRAPHY_DATA'
+              , DETAIL = 'An individual cannot be watched -- be followed,'
+                         || ' or have an attendance record, etc. --'
+                         || ' before they are born'
+                         || ': Key (AnimID) = ('
+                         || NEW.animid
+                         || '), Value (BirthDate) = ('
+                         || NEW.birthdate
+                         || ': Key (WATCHES.WID) = ('
+                         || a_wid
+                         || '), Value (WATCHES.Date) = ('
+                         || a_date
+                         || '), Value (WATCHES.Type) = ('
+                         || a_type
+                         || '), Value (WATCHES.CommID) = ('
+                         || a_commid
+                         || ')';
+        END IF;
+      END IF;
+
       -- Cannot have a watch period after study of the individual has ceased.
       IF NEW.departdate <> OLD.departdate THEN
         SELECT watches.wid, watches.commid, watches.date, watches.type
diff --git a/db/schemas/lib/triggers/create/watches.m4 b/db/schemas/lib/triggers/create/watches.m4
index 74b709e..19eabab 100644
--- a/db/schemas/lib/triggers/create/watches.m4
+++ b/db/schemas/lib/triggers/create/watches.m4
@@ -83,6 +83,35 @@ CREATE OR REPLACE FUNCTION watches_func ()
     END IF;
   END;
 
+  -- Cannot have a date before the individual was born
+  DECLARE
+    this_birthdate biography_data.birthdate%TYPE;
+
+  BEGIN
+    SELECT biography_data.birthdate
+      INTO this_birthdate
+      FROM biography_data
+      WHERE biography_data.animid = NEW.animid
+            AND biography_data.birthdate > NEW.date;
+    IF FOUND THEN
+      RAISE EXCEPTION integrity_constraint_violation USING
+            MESSAGE = 'Error on ' || TG_OP || ' of WATCHES'
+          , DETAIL = 'An individual cannot be followed before they are'
+                     || ' born'
+                     || ': Key (WID) = ('
+                     || NEW.wid
+                     || '), Value (AnimID) = ('
+                     || NEW.animid
+                     || '), Value (Date) = ('
+                     || NEW.Date
+                     || '), Value (CommID) = ('
+                     || NEW.commid
+                     || '), Value (BIOGRAPHY_DATA.BirthDate) = ('
+                     || this_birthdate
+                     || ')';
+    END IF;
+  END;
+
   -- Cannot have a follow date after the study of the individual has stopped
   DECLARE
     this_departdate biography_data.departdate%TYPE;
-- 
2.34.1